import { BusinessException } from '../../types/exceptions.js';
import { SysDisable } from '../../enums/system.js';
import { Pool as MySQLPool } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysAdmin,
  CreateSysAdminDto,
  UpdateSysAdminDto,
  SysAdminQueryDto,
} from '../../entities/system/SysAdmin.js';
import { BaseService } from '../BaseService.js';

/**
 * SysAdmin特定的数据库操作接口
 * @description 扩展基础数据库操作，添加管理员特有的查询方法
 */
interface SysAdminOperations extends DatabaseOperations<SysAdmin> {
  loadAdmin(adminName: string): Promise<SysAdmin | null>;
}

/**
 * MySQL数据库操作实现
 * @description 实现SysAdmin在MySQL数据库中的CRUD操作
 */
class MySQLOperations extends BaseMySQLOperations<SysAdmin> implements SysAdminOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(admin: CreateSysAdminDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_admin (
                      admin_name, nick_name, password, mobile, email, 
                      avatar, sex, dept_id, remark, status, ext_id,
                      time, update_time
                  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const params: (string | number)[] = [
      admin.admin_name as string,
      admin.nick_name as string,
      admin.password as string,
      admin.mobile as string,
      admin.email as string,
      admin.avatar as string,
      admin.sex as number,
      admin.dept_id as number,
      admin.remark as string,
      admin.status as number,
      admin.ext_id as string,
      now,
      now,
    ];
    return this.executeInsert(sql, params);
  }

  async update(id: number, admin: UpdateSysAdminDto): Promise<boolean> {
    const updateFields: string[] = [];
    const values: (string | number)[] = [];

    Object.entries(admin).forEach(([key, value]) => {
      if (value !== undefined && value !== null && key !== 'id') {
        updateFields.push(`${key} = ?`);
        values.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      throw new BusinessException('没有要更新的字段');
    }

    updateFields.push('update_time = ?');
    values.push(Date.now());
    values.push(id);

    const sql = `UPDATE sys_admin SET ${updateFields.join(', ')} WHERE id = ? AND disabled = ${SysDisable.ENABLE}`;
    return (await this.executeUpdate(sql, values)) > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = `UPDATE sys_admin SET disabled = ${SysDisable.DISABLE}, update_time = ? WHERE id = ?`;
    return (await this.executeUpdate(sql, [Date.now(), id])) > 0;
  }

  async loadAdmin(admin_name: string): Promise<SysAdmin | null> {
    const sql = 'SELECT * FROM sys_admin WHERE admin_name = ? AND disabled = ?';
    return this.queryOne(sql, [admin_name, SysDisable.ENABLE]);
  }

  async getById(id: number): Promise<SysAdmin | null> {
    const sql = 'SELECT * FROM sys_admin WHERE id = ? AND disabled = ?';
    return this.queryOne(sql, [id, SysDisable.ENABLE]);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysAdminQueryDto = {}
  ): Promise<{
    list: SysAdmin[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = [`disabled = ${SysDisable.ENABLE}`];
    const params: (string | number)[] = [];

    // 动态构建查询条件
    if (conditions.admin_name) {
      whereConditions.push('admin_name LIKE ?');
      params.push(`%${conditions.admin_name}%`);
    }
    if (conditions.nick_name) {
      whereConditions.push('nick_name LIKE ?');
      params.push(`%${conditions.nick_name}%`);
    }
    if (conditions.status !== undefined) {
      whereConditions.push('status = ?');
      params.push(conditions.status);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_admin WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    // 查询列表
    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_admin WHERE ${whereClause} ORDER BY time DESC LIMIT ? OFFSET ?`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
/**
 * PostgreSQL数据库操作实现
 * @description 实现SysAdmin在PostgreSQL数据库中的CRUD操作
 */
class PostgreSQLOperations
  extends BasePostgreSQLOperations<SysAdmin>
  implements SysAdminOperations
{
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(admin: CreateSysAdminDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_admin (
                   admin_name, nick_name, password, mobile, email, 
                   avatar, sex, dept_id, remark, status, ext_id,
                   time, update_time
                 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
                 RETURNING id`;
    const params: (string | number)[] = [
      admin.admin_name as string,
      admin.nick_name as string,
      admin.password as string,
      admin.mobile as string,
      admin.email as string,
      admin.avatar as string,
      admin.sex as number,
      admin.dept_id as number,
      admin.remark as string,
      admin.status as number,
      admin.ext_id as string,
      now,
      now,
    ];
    return this.executeInsert(sql, params);
  }

  async update(id: number, admin: UpdateSysAdminDto): Promise<boolean> {
    const updateFields: string[] = [];
    const values: (string | number)[] = [];
    let paramIndex = 1;

    Object.entries(admin).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id') {
        updateFields.push(`${key} = $${paramIndex++}`);
        values.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      throw new BusinessException('没有要更新的字段');
    }

    updateFields.push(`update_time = $${paramIndex++}`);
    values.push(Date.now());
    values.push(id);

    const sql = `UPDATE sys_admin SET ${updateFields.join(', ')} WHERE id = $${paramIndex} AND disabled = ${SysDisable.ENABLE}`;
    return (await this.executeUpdate(sql, values)) > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = `UPDATE sys_admin SET disabled = ${SysDisable.DISABLE}, update_time = $1 WHERE id = $2`;
    return (await this.executeUpdate(sql, [Date.now(), id])) > 0;
  }

  async loadAdmin(admin_name: string): Promise<SysAdmin | null> {
    const sql = 'SELECT * FROM sys_admin WHERE admin_name = $1 AND disabled = $2';
    return this.queryOne(sql, [admin_name, SysDisable.ENABLE]);
  }

  async getById(id: number): Promise<SysAdmin | null> {
    const sql = 'SELECT * FROM sys_admin WHERE id = $1 AND disabled = $2';
    return this.queryOne(sql, [id, SysDisable.ENABLE]);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysAdminQueryDto = {}
  ): Promise<{
    list: SysAdmin[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = [`disabled = ${SysDisable.ENABLE}`];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    // 动态构建查询条件
    if (conditions.admin_name) {
      whereConditions.push(`admin_name LIKE $${paramIndex++}`);
      params.push(`%${conditions.admin_name}%`);
    }
    if (conditions.nick_name) {
      whereConditions.push(`nick_name LIKE $${paramIndex++}`);
      params.push(`%${conditions.nick_name}%`);
    }
    if (conditions.status !== undefined) {
      whereConditions.push(`status = $${paramIndex++}`);
      params.push(conditions.status);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_admin WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    // 查询列表
    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_admin WHERE ${whereClause} ORDER BY time DESC LIMIT $${paramIndex++} OFFSET $${paramIndex}`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }
}

/**
 * 系统管理员服务类
 * @description 提供管理员相关的业务逻辑处理
 */
export class SysAdminService extends BaseService<
  SysAdmin,
  CreateSysAdminDto,
  UpdateSysAdminDto,
  SysAdminQueryDto,
  SysAdminOperations
> {
  constructor() {
    super(MySQLOperations, PostgreSQLOperations);
  }

  /**
   * 根据用户名加载管理员
   * @param adminName 管理员用户名
   * @returns 管理员信息或null
   */
  async loadAdmin(adminName: string): Promise<SysAdmin | null> {
    return this.getDbOperations().loadAdmin(adminName);
  }
}
